DROP TABLE #Device
DROP TABLE #FrequencyAlarms
DROP TABLE #VoltageAlarms
DROP TABLE #Computations

DELETE Alarm;
DELETE Measurement WHERE SignalTypeID IN (SELECT ID FROM SignalType WHERE Acronym LIKE 'ALRM' OR Acronym LIKE 'CALC')
DELETE CustomActionAdapter WHERE TypeName LIKE 'DynamicCalculator.DynamicCalculator'


/* Email Settings */
DECLARE @EmailConn VARCHAR(MAX)
SET @EmailConn = 'SMTPServer=dockerhost.gpa.gridprotectionalliance.org'

/* Email Settings */
DECLARE @ComputationCon VARCHAR(MAX)
SET @ComputationCon = 'FramesPerSecond=30; LagTime=1; LeadTime=10;'


/* Table of Devices */
SELECT 
	Device.ID AS DeviceID,
	Device.Acronym As Acronym,
	(SELECT TOP 1 PointTag FROM Measurement WHERE SignalTypeID IN (SELECT ID FROM SignalType WHERE Acronym LIKE 'FREQ') AND DeviceID = Device.ID) AS Frequency,
	(SELECT TOP 1 PointTag FROM Measurement WHERE SignalTypeID IN (SELECT ID FROM SignalType WHERE Acronym LIKE 'VPHM') AND DeviceID = Device.ID AND '+' IN (SELECT Phase FROM Phasor WHERE Phasor.DeviceID = Device.ID AND Phasor.SourceIndex = Measurement.PhasorSourceIndex)) AS Voltage,
	(SELECT MAX(BaseKV) FROM Phasor WHERE Phasor.DeviceID = Device.ID) AS BaseKV 
	INTO #Device FROM 
	Device WHERE IsConcentrator <> 1;

/* Table Of Frequency Alarms */
SELECT 
	CAST('Status ALARM Frequency < 45 Hz for ' AS VARCHAR(200)) AS Description,
	CAST('AL-LOW-FREQ-STATUS:' AS VARCHAR(200)) AS Prefix,
	21 AS Operation,
	45.00 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority INTO #FrequencyAlarms

INSERT INTO #FrequencyAlarms SELECT 
	'Status ALARM Frequency > 55 Hz for ' AS Description,
	'AL-HIGH-FREQ-STATUS:' AS Prefix,
	11 AS Operation,
	55.00 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'ALERT ALARM Frequency < 49.80 Hz for ' AS Description,
	'AL-LOW-FREQ-ALERT:' AS Prefix,
	21 AS Operation,
	49.80 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'ALERT ALARM Frequency > 50.20 Hz for ' AS Description,
	'AL-HIGH-FREQ-ALERT:' AS Prefix,
	11 AS Operation,
	50.20 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency > 50.10 Hz over 5 minutes for ' AS Description,
	'AL-HIGH-FREQ-WARN-SHORT:' AS Prefix,
	11 AS Operation,
	50.10 AS SetPoint,
	300 AS Delay,
	50 AS Priority
INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency > 50.05 Hz over 15 minutes for ' AS Description,
	'AL-HIGH-FREQ-WARN-LONG:' AS Prefix,
	11 AS Operation,
	50.05 AS SetPoint,
	900 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency < 49.90 Hz over 5 mintues for ' AS Description,
	'AL-LOW-FREQ-WARN-SHORT:' AS Prefix,
	21 AS Operation,
	49.90 AS SetPoint,
	300 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency < 49.95 Hz over 15 minutes for ' AS Description,
	'AL-LOW-FREQ-WARN-LONG:' AS Prefix,
	21 AS Operation,
	49.95 AS SetPoint,
	900 AS Delay,
	50 AS Priority


/* Table of Voltage Alarms */
SELECT 
	CAST('Status ALARM Voltage < 10KV for ' AS VARCHAR(200)) AS Description,
	CAST('AL-LOW-VOLT-STATUS:' AS VARCHAR(200)) AS Prefix,
	21 AS Operation,
	10000 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority,
	NULL AS BaseKV
	INTO #VoltageAlarms

/* Create Measurements for Alarms */

INSERT INTO Measurement (DeviceID, HistorianID, SignalTypeID, PointTag, SignalReference, Description, Enabled)
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'STAT'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'ALRM'),
	CONCAT(#VoltageAlarms.Prefix,#Device.Acronym),
	CONCAT(#VoltageAlarms.Prefix,#Device.Acronym),
	CONCAT(#VoltageAlarms.Description,#Device.Acronym),
	(SELECT 1)
	FROM #Device CROSS JOIN #VoltageAlarms WHERE #Device.BaseKV = #VoltageAlarms.BaseKV OR #VoltageAlarms.BaseKV IS NULL
UNION
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'STAT'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'ALRM'),
	CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym),
	CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym),
	CONCAT(#FrequencyAlarms.Description,#Device.Acronym),
	(SELECT 1)
	FROM #Device CROSS JOIN #FrequencyAlarms 

/* Create Alarms */

INSERT INTO Alarm (NodeID, TagName, Tolerance, SignalID, Delay, Description, Hysteresis, AssociatedMeasurementID, SetPoint, Severity, Operation) 
SELECT 
	(SELECT TOP 1 ID FROM Node), 
	CONCAT(#VoltageAlarms.Prefix,#Device.Acronym),
	NULL,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE #Device.Voltage),
	#VoltageAlarms.Delay, 
	CONCAT(#VoltageAlarms.Description, #Device.Acronym),
	1E-5,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE CONCAT(#VoltageAlarms.Prefix,#Device.Acronym)),
	#VoltageAlarms.SetPoint,
	#VoltageAlarms.Priority,
	#VoltageAlarms.Operation
	FROM #Device CROSS JOIN #VoltageAlarms WHERE #Device.BaseKV = #VoltageAlarms.BaseKV OR #VoltageAlarms.BaseKV IS NULL
UNION
SELECT 
	(SELECT TOP 1 ID FROM Node), 
	CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym),
	NULL,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE #Device.Frequency),
	#FrequencyAlarms.Delay, 
	CONCAT(#FrequencyAlarms.Description, #Device.Acronym),
	1E-5,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym)),
	#FrequencyAlarms.SetPoint,
	#FrequencyAlarms.Priority,
	#FrequencyAlarms.Operation
	FROM #Device CROSS JOIN #FrequencyAlarms 

/* Create Measurments for Computations STATUS, ALERT, WARNING*/
INSERT INTO Measurement (DeviceID, HistorianID, SignalTypeID, PointTag, SignalReference, Description, Enabled)
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'PPA'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'CALC'),
	CONCAT('AL-STATUS:',#Device.Acronym),
	CONCAT('AL-STATUS:',#Device.Acronym),
	CONCAT('Computed STATUS for ',#Device.Acronym),
	(SELECT 1)
	FROM #Device
UNION
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'PPA'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'CALC'),
	CONCAT('AL-FREQ-ALERT:',#Device.Acronym),
	CONCAT('AL-FREQ-ALERT:',#Device.Acronym),
	CONCAT('Computed Frequency ALERT for ',#Device.Acronym),
	(SELECT 1)
	FROM #Device
UNION
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'PPA'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'CALC'),
	CONCAT('AL-FREQ-WARN:',#Device.Acronym),
	CONCAT('AL-FREQ-WARN:',#Device.Acronym),
	CONCAT('Computed Frequency WARNING for ',#Device.Acronym),
	(SELECT 1)
	FROM #Device
	UNION
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'PPA'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'CALC'),
	CONCAT('AL-VOLT-ALERT:',#Device.Acronym),
	CONCAT('AL-VOLT-ALERT:',#Device.Acronym),
	CONCAT('Computed Voltage ALERT for ',#Device.Acronym),
	(SELECT 1)
	FROM #Device
UNION
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'PPA'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'CALC'),
	CONCAT('AL-VOLT-WARN:',#Device.Acronym),
	CONCAT('AL-VOLT-WARN:',#Device.Acronym),
	CONCAT('Computed Voltage WARNING for ',#Device.Acronym),
	(SELECT 1)
	FROM #Device

/* Generate STATUS Computations - Nothe that the computations are set up per APG Request modifications may need to be made */
INSERT INTO CustomActionAdapter (AssemblyName, TypeName, AdapterName, NodeID,Enabled,ConnectionString)
SELECT
'DynamicCalculator.dll',
'DynamicCalculator.DynamicCalculator',
CONCAT('STATUS-',#Device.Acronym),
(SELECT TOP 1 ID FROM Node),
(SELECT 1),
CONCAT(@ComputationCon,' OutputMeasurements=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-STATUS:',#Device.Acronym)),
	'; VariableList={fl=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-LOW-FREQ-STATUS:',#Device.Acronym)),
	';fh=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-HIGH-FREQ-STATUS:',#Device.Acronym)),
	';Vl=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-LOW-VOLT-STATUS:',#Device.Acronym)),
	'}; ExpressionText=', 'If(fl > 0.5, 0, If(fh > 0.5, 0,If(Vl > 0.5, 0,1)))')
FROM #Device UNION

SELECT
'DynamicCalculator.dll',
'DynamicCalculator.DynamicCalculator',
CONCAT('STATUS-',#Device.Acronym),
(SELECT TOP 1 ID FROM Node),
(SELECT 1),
CONCAT(@ComputationCon,' OutputMeasurements=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-FREQ-ALERT:',#Device.Acronym)),
'; VariableList={fl=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-LOW-FREQ-ALERT:',#Device.Acronym)),
';fh=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-HIGH-FREQ-ALERT:',#Device.Acronym)),
';s=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-STATUS:',#Device.Acronym)),'}; ExpressionText=', 'If(s < 0.5, 0, If(fh > 0.5, 1,If(fl > 0.5, -1,0)))')
FROM #Device UNION
SELECT
'DynamicCalculator.dll',
'DynamicCalculator.DynamicCalculator',
CONCAT('STATUS-',#Device.Acronym),
(SELECT TOP 1 ID FROM Node),
(SELECT 1),
CONCAT(@ComputationCon,' OutputMeasurements=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-FREQ-ALERT:',#Device.Acronym)),
	'; VariableList={fls=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-LOW-FREQ-WARN-SHORT:',#Device.Acronym)),
	';fhs=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-HIGH-FREQ-WARN-SHORT:',#Device.Acronym)),
	';fhl=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-HIGH-FREQ-WARN-LONG:',#Device.Acronym)),
	';fls=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-LOW-FREQ-WARN-LONG:',#Device.Acronym)),
	';s=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-STATUS:',#Device.Acronym)),
	';al=',(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT('AL-FREQ-ALERT:',#Device.Acronym)),
	'}; ExpressionText=', 'If(s < 0.5, 0, If(al > 0.5,0,If(fhs > 0.5, 1,If(fls > 0.5, -1,If(fhl > 0.5, 2, If(fll > 0.5,-2, 0))))))')
FROM #Device 


-- Enable All Alarms
UPDATE ALARM SET Enabled = 1